package com.xinchuang.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinchuang.domain.pojo.TestNoticeTimeTable;
import com.xinchuang.domain.vo.PackingOut;
import com.xinchuang.entity.api.MonthTimes;
import com.xinchuang.entity.api.SubTargetVo;
import org.apache.ibatis.annotations.*;

import java.time.MonthDay;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * TODO
 *
 * @author zkwczx
 * @version 1.0
 * @date 2023/7/7 14:07
 */
@Mapper
public interface StasticsMapper {

    @Select(value = "SELECT MAX(try_out_times) AS value FROM test_model_sub_table WHERE mold_id = #{moldId}")
    Integer getMoldMaxTryTimes(String moldId);

    @Select(value = {
            "<script>",
            "SELECT nmt.id,nmt.parent_plan_sub_id AS subId,",
            "mst.try_out_times AS tryOutTimes,",
            "nmt.project_name AS projectName,",
            "mst.mold_id AS moldId,",
            "mp.mold_name AS moldName,",
            "mp.mold_weight AS moldWeight,",
            "mp.customer_name AS customerName",
            "FROM test_notice_main_tabale nmt",
            "JOIN test_model_sub_table mst",
            "ON nmt.parent_plan_sub_id = mst.plan_id",
            "JOIN test_model_param mp",
            "ON mst.mold_id = mp.mold_id",
            "WHERE 1=1 AND nmt.status IN (1,2,3,4)",
            "<if test='moldId != null'>",
            "AND mst.mold_id LIKE '%${moldId}%'",
            "</if>",
            "<if test='projectName != null'>",
            "AND nmt.project_name LIKE '%${projectName}%'",
            "</if>",
            "</script>"
    })
    IPage<Map<String, Object>> getMoldMain(Page page, @Param("moldId") String moldId, @Param("projectName") String projectName);

    @Select(value = {
            "<script>",
            "SELECT nmt.id,mp.project_name,mst.mold_id,mp.mold_name,mp.mold_weight,mp.customer_name,       ",
            "ct.type,ct.measurement,ct.stock,ct.consumption,ct.specification,ct.claim,",
            "tt.production_quantity,tt.modulus_quantity,tt.qualified_quantity,tt.receive_quantity,",
            "ntt.reached_time",
            "FROM test_notice_main_tabale nmt",
            "JOIN test_model_sub_table mst",
            "ON nmt.parent_plan_sub_id = mst.plan_id",
            "JOIN test_model_param mp",
            "ON mst.mold_id = mp.mold_id",
            "LEFT JOIN test_notice_time_table ntt ON ntt.parent_notice_id = nmt.id",
            "LEFT JOIN test_notice_consumable_table ct ON nmt.id = ct.parent_notice_id",
            "LEFT JOIN test_notice_production_type_table tt ON nmt.id = tt.parent_notice_id",
            "WHERE 1=1",
            "<if test='moldId != null'>",
            "AND mst.mold_id LIKE '%${moldId}%'",
            "</if>",
            "<if test='projectName != null'>",
            "AND nmt.project_name LIKE '%${projectName}%'",
            "</if>",
            "</script>"
    })
    @Results(
            @Result(property = "reachedTime", column = "reached_time", javaType = Date.class)
    )
    IPage<PackingOut> packingOut(Page page, @Param("moldId") String moldId, @Param("projectName") String projectName);

    @Select(value = "SELECT customer_name FROM test_model_param GROUP BY customer_name")
    List<String> getMoldCustomerName();

    @Select(value = {
//            "SELECT ct.specification,SUM(ct.claim) AS claim,SUM(ct.stock) AS stock,SUM(ct.consumption) AS consumption",
//            "FROM test_notice_main_tabale nmt",
//            "JOIN test_notice_consumable_table ct ON nmt.id = ct.parent_notice_id",
//            "WHERE nmt.project_name = #{projectName}",
//            "GROUP BY ct.specification"
            "SELECT a.specification,a.type,SUM(a.claim) AS claim,SUM(a.stock) AS stock,SUM(a.consumption) AS consumption,SUM(a.qualified_quantity) AS qualified FROM (SELECT ct.specification,ct.type,ct.claim,ct.stock,ct.consumption,tnptt.qualified_quantity",
            "FROM test_notice_main_tabale nmt",
            "JOIN test_notice_consumable_table ct ON nmt.id = ct.parent_notice_id",
            "JOIN test_notice_production_type_table tnptt ON nmt.id = tnptt.parent_notice_id",
            "WHERE nmt.project_name = #{projectName}) AS a GROUP BY a.type,a.specification"
    })
    @Results(value = {
            @Result(column = "specification", property = "specification", javaType = String.class),
            @Result(column = "type", property = "type", javaType = String.class),
            @Result(column = "claim", property = "claim", javaType = Integer.class),
            @Result(column = "stock", property = "stock", javaType = Integer.class),
            @Result(column = "consumption", property = "consumption", javaType = Integer.class),
            @Result(column = "qualified", property = "qualified", javaType = Integer.class)
    })
    List<Map<String, Object>> getMainConsumable(@Param("projectName") String projectName);

    @Select(value = {
            "SELECT SUM(ct.qualified_quantity)",
            "FROM test_notice_main_tabale nmt",
            "JOIN test_notice_production_type_table ct ON nmt.id = ct.parent_notice_id",
            "WHERE nmt.project_name = #{projectName}"
    })
    Long getOkWay(@Param("projectName") String projectName);

    @Select(value = {
            "SELECT SUM(target_count) FROM try_out_record WHERE note_id IN ",
            "(SELECT id FROM test_notice_main_tabale mm ",
            "JOIN test_model_sub_table ss ON mm.parent_plan_sub_id = ss.plan_id",
            "WHERE ss.mold_id = #{moldId})"
    })
    Integer getTestMoldNumber(@Param("moldId") String moldId);

    @Select(value = {
            "SELECT ntt.* FROM test_notice_main_tabale nmt",
            "JOIN test_notice_time_table ntt ON nmt.id = ntt.parent_notice_id",
            "WHERE nmt.`status` IN ('3','4')",
            "AND ntt.update_time BETWEEN #{start} AND #{end}"
    })
    List<TestNoticeTimeTable> getMonthTimeTable(@Param("start") String start, @Param("end") String end);

    //weekYearMonthStastics start

    @Select(value = {
            "SELECT COUNT(1) FROM test_model_param mp JOIN test_model_sub_table mst ON mp.mold_id = mst.mold_id",
            "WHERE mp.customer_name = #{projectName} AND mst.create_time BETWEEN #{start} AND #{end}"
    })
    Integer getTestNumAll(@Param("projectName") String projectName, @Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT COUNT(1) FROM test_model_param mp JOIN test_model_sub_table mst ON mp.mold_id = mst.mold_id",
            "WHERE mp.customer_name = #{projectName} AND mst.try_out_times = 0 AND mst.create_time BETWEEN #{start} AND #{end}"
    })
    Integer getTestNumT0All(@Param("projectName") String projectName, @Param("start") String start, @Param("end") String end);


    @Select(value = {
            "SELECT COUNT(1) FROM test_model_param mp JOIN test_model_sub_table mst ON mp.mold_id = mst.mold_id",
            "WHERE mp.customer_name = #{projectName} AND mst.create_time BETWEEN #{start} AND #{end}",
            "AND mst.planned_time IS NOT NULL",
            "AND mst.practical_time IS NOT NULL",
            "AND DATE_FORMAT(mst.planned_time,'%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(mst.practical_time,'%Y-%m-%d %H:%i:%s')"
    })
    Integer getTestDoneNumAll(@Param("projectName") String projectName, @Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT COUNT(1) FROM test_model_param mp JOIN test_model_sub_table mst ON mp.mold_id = mst.mold_id",
            "WHERE mp.customer_name = #{projectName} AND mst.try_out_times = 0 AND mst.create_time BETWEEN #{start} AND #{end}",
            "AND mst.planned_time IS NOT NULL",
            "AND mst.practical_time IS NOT NULL",
            "AND DATE_FORMAT(mst.planned_time,'%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(mst.practical_time,'%Y-%m-%d %H:%i:%s')"
    })
    Integer getTestDoneNumT0All(@Param("projectName") String projectName, @Param("start") String start, @Param("end") String end);

    //weekYearMonthStastics end

    @Select(value = {
//            "SELECT mold_id AS moldId,MAX(try_out_times) AS tryTimes FROM test_model_sub_table WHERE practical_time BETWEEN #{start} AND #{end} GROUP BY mold_id"
            "SELECT mold_id AS moldId,try_out_times AS tryTimes  FROM test_model_sub_table WHERE practical_time BETWEEN #{start} AND #{end}"
    })
    @Results(value = {
            @Result(property = "moldId", column = "moldId", javaType = String.class),
            @Result(property = "tryTimes", column = "tryTimes", javaType = Integer.class)
    })
    List<MonthTimes> getMonthTryOutTimeSum(@Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT COUNT(1) FROM (",
//            "SELECT mold_id AS moldId,IFNULL(MAX(try_out_times),0) AS times FROM test_model_sub_table WHERE practical_time BETWEEN #{start} AND #{end} GROUP BY mold_id) AS a",
            "SELECT mold_id AS moldId,try_out_times AS times FROM test_model_sub_table WHERE practical_time BETWEEN #{start} AND #{end}) AS a",
            "WHERE a.times < 6"
    })
    int getFiveTestTimes(@Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT a.times,b.target_count AS counts FROM (",
            "SELECT mold_id AS moldId,IFNULL(MAX(try_out_times),0) AS times FROM test_model_sub_table WHERE practical_time BETWEEN #{start} AND #{end} GROUP BY mold_id) AS a",
            "JOIN test_model_param b ON a.moldId = b.mold_id WHERE b.target_count IS NOT NULL"
    })
    @Results(value = {
            @Result(property = "times", column = "times", javaType = Integer.class),
            @Result(property = "counts", column = "counts", javaType = Integer.class)
    })
    List<Map<String, Integer>> getAllTestAndTargetCoutes(@Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT a.id,IFNULL(b.normal_time_price, 0) AS price ",
            "FROM test_notice_main_tabale a ",
            "JOIN try_out_device_mod b ON a.try_out_device_id = b.device_id",
            "JOIN test_model_sub_table c ON c.plan_id = a.parent_plan_sub_id",
            "WHERE c.practical_time BETWEEN #{start} AND #{end}"
    })
    @Results(value = {@Result(property = "price", column = "price", javaType = String.class)})
    List<Map<String, String>> getMonthPrice(@Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT a.moldId FROM (",
            "SELECT a.moldId,a.times,b.target_count AS counts FROM (",
            "SELECT mold_id AS moldId,IFNULL(MAX(try_out_times),0) AS times FROM test_model_sub_table WHERE practical_time BETWEEN #{start} AND #{end} GROUP BY mold_id) AS a",
            "JOIN test_model_param b ON a.moldId = b.mold_id WHERE b.target_count IS NOT NULL) AS a WHERE a.counts >= a.times"
    })
    List<String> getMonthTrueMoldId(@Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT a.id,IFNULL(c.normal_time_price,0) AS price",
            "FROM test_notice_main_tabale a",
            "JOIN test_model_sub_table b ON a.parent_plan_sub_id = b.plan_id",
            "JOIN try_out_device_mod c ON a.try_out_device_id = c.device_id",
            "WHERE b.mold_id = #{moldId} ",
            "AND b.practical_time BETWEEN #{start} AND #{end}",
    })
    @Results(value = {@Result(property = "price", column = "price", javaType = String.class)})
    List<Map<String, String>> getMonthTruePriceId(@Param("moldId") String moldId, @Param("start") String start, @Param("end") String end);

    @Select(value = "SELECT IFNULL(SUM(intervals),0) FROM test_notice_time WHERE parent_notice_id = #{noteId} AND `status` IN (1,2,3,4,5,6,7)")
    long getTimeSumTime(String noteId);

    @Select(value = "SELECT IFNULL(SUM(intervals),0) FROM test_notice_time WHERE parent_notice_id = #{noteId} AND `status` IN (2,5,6)")
    long getTimeSumErrorTime(String noteId);

    @Select(value = {
            "SELECT a.`status`,IFNULL(SUM(a.intervals),0) AS intervals FROM test_notice_time a ",
            "JOIN test_notice_main_tabale b ON a.parent_notice_id = b.id",
            "JOIN test_model_sub_table c ON b.parent_plan_sub_id = c.plan_id",
            "WHERE b.create_time BETWEEN #{start} AND #{end} GROUP BY a.`status`"
    })
    @Results(value = {
            @Result(property = "status", column = "status", javaType = Long.class),
            @Result(property = "intervals", column = "intervals", javaType = Long.class)
    })
    List<Map<String, Long>> getMonthTimeAll(@Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT a.sub_target FROM test_notice_main_tabale a ",
            "JOIN test_model_sub_table b ON a.parent_plan_sub_id = b.plan_id",
            "WHERE b.practical_time BETWEEN #{start} AND #{end} ",
            "AND LENGTH(a.sub_target) != 0 GROUP BY a.sub_target",
    })
    List<String> getMonthSubTargets(@Param("start") String start, @Param("end") String end);

    @Select(value = {
            "SELECT a.id,a.try_out_device_id,a.sub_target,b.try_out_times,c.price,c.normal_time_price",
            "FROM test_notice_main_tabale a",
            "JOIN test_model_sub_table b ON a.parent_plan_sub_id = b.plan_id",
            "JOIN try_out_device_mod c ON a.try_out_device_id = c.device_id",
            "WHERE b.practical_time BETWEEN #{start} AND #{end}"
    })
    List<SubTargetVo> getSubTargetAll(@Param("start") String start, @Param("end") String end);

    @Select(value = "SELECT SUM(intervals) FROM test_notice_time WHERE parent_notice_id = #{mainId}")
    Long getTimeSum(@Param("mainId") String mainId);

    @Select(value = "SELECT b.planned_time FROM test_notice_main_tabale a JOIN test_model_sub_table b ON a.parent_plan_sub_id = b.plan_id WHERE b.try_out_times = 0 AND a.parent_plan_sub_id IS NOT NULL AND b.mold_id = #{moldId} LIMIT 1")
    @Results(value = {
            @Result(property = "plannedTime", column = "planned_time", javaType = Date.class)
    })
    Date getPlanTimeByMold(@Param("moldId") String moldId);

}
